import path from 'path'
import { app } from 'electron'
import Database from 'better-sqlite3'
import sql from './sql.js'
import fs from 'fs'
import log from '../logger.js'
import { update, findByKey, insert } from '../dao/context.js'

const userDataPath = app.getPath('userData')
const dbPath = path.join(userDataPath, 'biz.db')

// 保持一个全局的数据库连接
let dbInstance = null

export function initDB() {
  const db = new Database(dbPath);
  let needInit = false;

  // 检查数据库是否为新创建的
  try {
    // 尝试查询 context 表，如果不存在会抛出错误
    db.prepare('SELECT * FROM context LIMIT 1').all();
    log.info('Database already initialized.');
  } catch (error) {
    // 如果表不存在，说明需要初始化
    if (error.message.includes('no such table')) {
      needInit = true;
      log.info('Database needs initialization.');
    } else {
      // 其他错误则直接抛出
      log.error('Error checking database:', error);
      throw error;
    }
  }

  if (needInit) {
    log.info('Initializing database at:', dbPath);
    // 执行初始化脚本（版本1）
    db.exec(sql[0].script);
    log.info('Database initialized to version 1');
  }

  // 关闭临时连接
  db.close();

  // 更新数据库到最新版本
  updateDB();
}

function updateDB() {
  const db = connect();

  try {
    // 尝试获取当前版本
    const dbVersionRecord = findByKey('db_version');
    let currentVersion = dbVersionRecord ? parseInt(dbVersionRecord.val, 10) : 0;
    log.info('Current database version:', currentVersion);

    // 筛选需要执行的脚本
    const scriptsToRun = sql.filter(item => item.version > currentVersion);

    if (scriptsToRun.length > 0) {
      log.info(`Found ${scriptsToRun.length} update scripts to run`);

      // 逐个执行更新脚本
      scriptsToRun.forEach(item => {
        try {
          log.info(`Updating database to version ${item.version}`);
          db.exec(item.script);

          // 更新版本号
          if (currentVersion === 0) {
            // 如果版本为0（首次安装），context表可能刚刚创建
            insert('db_version', item.version.toString());
          } else {
            // 否则更新现有记录
            update('db_version', item.version.toString());
          }

          currentVersion = item.version;
          log.info(`Successfully updated to version ${item.version}`);
        } catch (error) {
          log.error(`Error executing update script for version ${item.version}:`, error);
          throw error; // 中断更新流程
        }
      });
    } else {
      log.info('Database is already at the latest version');
    }
  } catch (error) {
    log.error('Error during database update:', error);
    throw error;
  }
}

export function connect() {
  if (!dbInstance) {
    // 创建新连接
    dbInstance = new Database(dbPath, {
      fileMustExist: false
    })

    // 优化配置
    // dbInstance.pragma('journal_mode = WAL')
    // dbInstance.pragma('synchronous = NORMAL')

    // 包装原始方法以添加日志
    const originalPrepare = dbInstance.prepare.bind(dbInstance)
    dbInstance.prepare = function (sql) {
      const stmt = originalPrepare(sql)
      const originalRun = stmt.run.bind(stmt)
      const originalGet = stmt.get.bind(stmt)
      const originalAll = stmt.all.bind(stmt)

      // 包装 run 方法
      stmt.run = function (...args) {
        const options = args[args.length - 1]
        const shouldLog = !(options && typeof options === 'object' && options.silent === true)

        if (shouldLog) {
          log.debug('[SQL Run]:', sql, args)
        }

        // 如果最后一个参数是选项对象，则移除它
        const sqlArgs = options && typeof options === 'object' ? args.slice(0, -1) : args
        return originalRun(...sqlArgs)
      }

      // 包装 get 方法
      stmt.get = function (...args) {
        const options = args[args.length - 1]
        const shouldLog = !(options && typeof options === 'object' && options.silent === true)

        if (shouldLog) {
          log.debug('[SQL Get]:', sql, args)
        }

        const sqlArgs = options && typeof options === 'object' ? args.slice(0, -1) : args
        return originalGet(...sqlArgs)
      }

      // 包装 all 方法
      stmt.all = function (...args) {
        const options = args[args.length - 1]
        const shouldLog = !(options && typeof options === 'object' && options.silent === true)

        if (shouldLog) {
          log.debug('[SQL All]:', sql, args)
        }

        const sqlArgs = options && typeof options === 'object' ? args.slice(0, -1) : args
        return originalAll(...sqlArgs)
      }

      return stmt
    }

    log.info('[DB] Connected:', dbPath)
  }
  return dbInstance
}
